import pandas as pd  # 高性能数据结构与分析工具 :contentReference[oaicite:0]{index=0}
from sklearn.preprocessing import MinMaxScaler  # 归一化工具 :contentReference[oaicite:1]{index=1}

# 1. 读取数据
df = pd.read_excel(
    "Media_6Metrics_DeviationSummary_new.xlsx",
    usecols=["Media", "ArithmeticMean", "EventNumber"],
    engine="openpyxl"
)  # openpyxl 常用读取引擎 :contentReference[oaicite:2]{index=2}

# 2. 初始化归一化器
scaler = MinMaxScaler()

# 3. 执行归一化
df[["Dev_norm", "Num_norm"]] = scaler.fit_transform(
    df[["ArithmeticMean", "EventNumber"]]
)  # MinMaxScaler.fit_transform 同时拟合并转换 :contentReference[oaicite:3]{index=3}

# 4. 计算残差：偏离归一化值 – 产出归一化值
df["ResidualDeviation"] = df["Dev_norm"] - df["Num_norm"]

# 5. 保存结果
df.to_excel("Media_OutputDeviation.xlsx", index=False)  # 输出到 Excel :contentReference[oaicite:4]{index=4}

print("✅ 已生成“Media_OutputDeviation.xlsx”，包含 Media, ArithmeticMean, EventNumber, Dev_norm, Num_norm, ResidualDeviation")
